package DAO;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.ClassModel;
import model.Student;
import util.DataUtil;

public class StudentDAO {

    public static List<Student> getAll() {
        List<Student> list = new ArrayList<Student>();
        String call = "{Call GET_ALL_STUDENT}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Student st = new Student();
                st.setStudentId(rs.getString("StudentID"));
                st.setFullName(rs.getString("FullName"));
                st.setBirthDay(rs.getString("BirthDay"));
                st.setGender(rs.getBoolean("Gender"));
                st.setEmail(rs.getString("Email"));
                st.setPhone(rs.getString("Phone"));
                st.setAddress(rs.getString("Address"));
                ClassModel clm = new ClassModel();
                clm.setClassId(rs.getString("ClassID"));
                clm.setClassName(rs.getString("ClassName"));
                st.setClassModel(clm);
                list.add(st);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return list;
    }

    public static List<Student> getStudentByClass(String ClassID) {
        List<Student> list = new ArrayList<Student>();
        String call = "select * from Student where ClassID = ?";
        try {
            PreparedStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setString(1, ClassID);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Student st = new Student();
                st.setStudentId(rs.getString("StudentID"));
                st.setFullName(rs.getString("FullName"));
                list.add(st);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return list;
    }

    public static boolean checkStudentID(String id) {
        boolean fl = true;
        String call = "{Call CHECK_STUDENT_ID(?)}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setString(1, id);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                fl = false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return fl;
    }

    public static boolean addStudent(Student st) {
        int rs = 0;
        String call = "{Call ADD_STUDENT(?,?,?,?,?,?,?,?)}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setString(1, st.getStudentId());
            cs.setString(2, st.getFullName());
            cs.setString(3, st.getBirthDay());
            cs.setBoolean(4, st.isGender());
            cs.setString(5, st.getEmail());
            cs.setString(6, st.getPhone());
            cs.setString(7, st.getAddress());
            cs.setString(8, st.getClassModel().getClassId());
            rs = cs.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return (rs > 0) ? true : false;
    }

    public static boolean editStudent(Student st) {
        int rs = 0;
        String call = "{Call EDIT_STUDENT(?,?,?,?,?,?,?,?)}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setString(1, st.getStudentId());
            cs.setString(2, st.getFullName());
            cs.setString(3, st.getBirthDay());
            cs.setBoolean(4, st.isGender());
            cs.setString(5, st.getEmail());
            cs.setString(6, st.getPhone());
            cs.setString(7, st.getAddress());
            cs.setString(8, st.getClassModel().getClassId());
            rs = cs.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return (rs > 0) ? true : false;
    }

    public static boolean deleteStudent(String id) {
        int rs = 0;
        String call = "{Call DELETE_STUDENT(?)}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setString(1, id);
            rs = cs.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return (rs > 0) ? true : false;
    }
    
    public static List<Student> search(String filter,String key,List<Student> listStudent){
        List<Student> list = new ArrayList<Student>();
        for(Student st:listStudent){
            if(filter.equals("Student ID")){
                if((st.getStudentId().toLowerCase()).contains(key.toLowerCase())){
                   list.add(st);
                }
            }
            if(filter.equals("Student Name")){
                if((st.getFullName().toLowerCase()).contains(key.toLowerCase())){
                   list.add(st);
                }
            }
            if(filter.equals("Class Name")){
                if((st.getClassModel().getClassName().toLowerCase()).contains(key.toLowerCase())){
                   list.add(st);
                }
            }
            if(filter.equals("Email")){
                if((st.getEmail().toLowerCase()).contains(key.toLowerCase())){
                   list.add(st);
                }
            }
            if(filter.equals("Phone")){
                if((st.getPhone().toLowerCase()).contains(key.toLowerCase())){
                   list.add(st);
                }
            }
            if(filter.equals("Address")){
                if((st.getAddress().toLowerCase()).contains(key.toLowerCase())){
                   list.add(st);
                }
            }
        }
        return list;
    }
}
